
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LogLevelFetchByLevel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LogLevelFetchByLevel]
GO

CREATE PROCEDURE [LogLevelFetchByLevel]
	@Level text
AS
BEGIN

    -- Insert statements for procedure here
	SELECT * FROM [LogLevel]
	WHERE [Level] LIKE @Level;
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompanyListFetchByRegion]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CompanyListFetchByRegion]
GO

CREATE PROCEDURE [dbo].[CompanyListFetchByRegion]
	-- Add the parameters for the stored procedure here
	@RegionId int,
	@StartIndex int = 0,
	@MaxRows int = -1,
	@SortField varchar(35) = null,
	@SortOrder int = 0,
	@TotalRows int = 0 output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @Temp TABLE
	(
		[TempId] int IDENTITY(1,1) PRIMARY KEY,
		[CompanyId] int,
		[Name] varchar(50),
		[Url] varchar(128),
		[Phone] varchar(16)
	)

	INSERT INTO @Temp ([CompanyId],[Name],[Url],[Phone])
	SELECT C.[CompanyId], C.[Name], C.[Url], C.[Phone]
	FROM [Company] AS C INNER JOIN [CompaniesAddresses] AS CA
	ON C.[CompanyId]=CA.[CompanyCompanyId] INNER JOIN [Address] AS A
	ON CA.[AddressAddressId]=A.[AddressId]
	WHERE A.[RegionId]=@RegionId;

	-- Get the total row count, used for paging.
	SET @TotalRows = (SELECT COUNT(TempId) FROM @Temp);

	-- If paging is specified
	IF (@MaxRows < 1)
	BEGIN
		-- return all rows if MaxRows is 0 or less
		SET @MaxRows = @TotalRows;
		SET @StartIndex = 0;
	END
	
	IF @SortOrder = 2
	BEGIN
		SELECT [CompanyId],[Name],[Url],[Phone] FROM @Temp 
		WHERE [TempId] > @StartIndex
		AND [TempId] < @StartIndex + @MaxRows + 1
		ORDER BY 
		CASE @SortField
			WHEN 'Name' THEN cast ([Name] as sql_variant)
			WHEN 'Url' THEN cast ([Url] as sql_variant)
			WHEN 'Phone' THEN cast ([Phone] as sql_variant)
			ELSE [CompanyId]
		END
		DESC
	END
	ELSE
	BEGIN
		SELECT [CompanyId],[Name],[Url],[Phone] FROM @Temp 
		WHERE [TempId] > @StartIndex
		AND [TempId] < @StartIndex + @MaxRows + 1
		ORDER BY 
		CASE @SortField
			WHEN 'Name' THEN cast ([Name] as sql_variant)
			WHEN 'Url' THEN cast ([Url] as sql_variant)
			WHEN 'Phone' THEN cast ([Phone] as sql_variant)
			ELSE [CompanyId]
		END
		ASC
	END
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompanySearchByName]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CompanySearchByName]
GO

CREATE PROCEDURE [dbo].[CompanySearchByName]
	-- Add the parameters for the stored procedure here
	@Name varchar(50),
	@StartIndex int = 0,
	@MaxRows int = -1,
	@SortField varchar(35) = null,
	@SortOrder int = 0,
	@TotalRows int = 0 output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @Temp TABLE
	(
		[TempId] int IDENTITY(1,1) PRIMARY KEY,
		[CompanyId] int,
		[Name] varchar(50),
		[Url] varchar(128),
		[Phone] varchar(16)
	)
	INSERT INTO @Temp ([CompanyId],[Name],[Url],[Phone])
	SELECT C.[CompanyId],C.[Name],C.[Url],C.[Phone]
	FROM [Company] as C
	WHERE C.[Name] LIKE '%' + @Name + '%';
		
	-- If paging is specified
	IF (@MaxRows < 1)
	BEGIN
		-- return all rows if MaxRows is 0 or less
		SET @MaxRows = @TotalRows;
		SET @StartIndex = 0;
	END
	
	IF @SortOrder = 2
	BEGIN
		SELECT * FROM @Temp 
		WHERE TempId >= @StartIndex 
		AND TempId < @StartIndex + @MaxRows + 1
		ORDER BY
		CASE @SortField
			WHEN 'Name' THEN cast ([Name] as sql_variant)
			WHEN 'Url' THEN cast (Url as sql_variant)
			WHEN 'Phone' THEN cast (Phone as sql_variant)
			ELSE [CompanyId]
		END
		DESC -- descending
	END
	ELSE -- Sort order == 1 or 0
	BEGIN
		SELECT * FROM @Temp 
		WHERE TempId >= @StartIndex 
		AND TempId < @StartIndex + @MaxRows + 1
		ORDER BY
		CASE @SortField
			WHEN 'Name' THEN cast ([Name] as sql_variant)
			WHEN 'Url' THEN cast (Url as sql_variant)
			WHEN 'Phone' THEN cast (Phone as sql_variant)
			ELSE [CompanyId]
		END
		ASC -- ascending
	END

	-- Get the total row count, used for paging.
	SET @TotalRows = (SELECT COUNT(TempId) FROM @Temp);
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RegionFetchByAbbreviation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RegionFetchByAbbreviation]
GO

CREATE PROCEDURE [dbo].[RegionFetchByAbbreviation]
	@Abbreviation varchar(2),
	@StartIndex int = 0,
	@MaxRows int = -1,
	@SortField varchar(35) = null,
	@SortOrder int = 0,
	@TotalRows int = 0 output
AS
BEGIN
	SELECT * FROM [Region]
	WHERE [Abbreviation]=@Abbreviation ORDER BY [Abbreviation];
END
GO


